#!/bin/bash

CK_HOST='127.0.0.1'
CK_USER='default'
CK_PWD='123456'
CK_PORT=9000
OUTDIR=$(pwd)
# 排除库
SKIP_DATABASES=("system" "information_schema" "INFORMATION_SCHEMA")
# 排除表
SKIP_TABLES=("abc.test1" "abc.test2" "default.testa")

OUTDIR_RESULT=$OUTDIR/export_data

# 导出表
function export_table_schema(){
    local DB_NAME=$(echo "$1"|awk -F '.' '{print $1}')
    local TABLE=$(echo "$1"|awk -F '.' '{print $2}')
    local SCHEMA_RESULT=$OUTDIR_RESULT/$DB_NAME/schema
    mkdir -p $SCHEMA_RESULT

    echo "export table schema $DB_NAME.$TABLE "
    #echo "clickhouse-client -h $CK_HOST -u $CK_USER --password=\"${CK_PWD}\" --port=${CK_PORT} -q \"SHOW CREATE TABLE ${DB_NAME}.${TABLE} FORMAT TabSeparatedRaw\" > ${SCHEMA_RESULT}/${DB_NAME}.${TABLE}.tsv"
    clickhouse-client -h $CK_HOST -u $CK_USER --password="${CK_PWD}" --port=${CK_PORT} -q "SHOW CREATE TABLE ${DB_NAME}.${TABLE} FORMAT TabSeparatedRaw" > ${SCHEMA_RESULT}/${DB_NAME}.${TABLE}.tsv
    echo ";" >> ${SCHEMA_RESULT}/${DB_NAME}.${TABLE}.tsv
}

# 导出表
function export_table_data(){
    local DB_NAME=$(echo "$1"|awk -F '.' '{print $1}')
    local TABLE=$(echo "$1"|awk -F '.' '{print $2}')
    local DATA_RESULT=$OUTDIR_RESULT/$DB_NAME/data
    mkdir -p $DATA_RESULT

    echo "export table data $DB_NAME.$TABLE "
    #echo "clickhouse-client -h $CK_HOST -u $CK_USER --password=\"${CK_PWD}\" --port=${CK_PORT} -q \"select * from ${DB_NAME}.${TABLE} FORMAT CSVWithNames\"  --format_csv_delimiter=',' > ${DATA_RESULT}/${DB_NAME}.${TABLE}.csv"
    clickhouse-client -h $CK_HOST -u $CK_USER --password="${CK_PWD}" --port=${CK_PORT} -q "select * from ${DB_NAME}.${TABLE} FORMAT CSVWithNames"  --format_csv_delimiter=',' > ${DATA_RESULT}/${DB_NAME}.${TABLE}.csv
}

# 导出库下的所有表结构
function export_db_table_schema(){
    local DB_NAME=$1
    local SCHEMA_RESULT=$OUTDIR_RESULT/$DB_NAME/schema
    mkdir -p $SCHEMA_RESULT

    get_db_all_tables $DB_NAME $SCHEMA_RESULT

    while read -r TABLE  
        do
            # 物化视图不导出
            if [[ "$TABLE" == ".inner_id."* ]]; then
            echo "skip materialized view $DB_NAME.$TABLE "
            continue ;
            fi
            
            # dump schema
            export_table_schema $DB_NAME.$TABLE

    done  < `ls ${SCHEMA_RESULT}/table.txt`
}

# 导出库下的所有表数据
function export_db_table_data(){
    local DB_NAME=$1
    local DATA_RESULT=$OUTDIR_RESULT/$DB_NAME/data
    mkdir -p $DATA_RESULT

    get_db_all_tables $DB_NAME $DATA_RESULT

    while read -r TABLE  
        do
            # 物化视图不导出
            if [[ "$TABLE" == ".inner_id."* ]]; then
            echo "skip materialized view $DB_NAME.$TABLE "
            continue ;
            fi
            
            # dump schema
            export_table_data $DB_NAME.$TABLE

    done  < `ls ${DATA_RESULT}/table.txt`
}

# 导出所有库的表
function  export_db_table_schema_all(){
    local SCHEMA_RESULT=$OUTDIR_RESULT
    mkdir -p $SCHEMA_RESULT

    get_all_db_name $SCHEMA_RESULT

    while read -r DB 
    do
      export_db_table_schema $DB
    done  < `ls ${SCHEMA_RESULT}/db.txt`
}

# 导出所有库的表数据
function  export_db_table_data_all(){
    local DATA_RESULT=$OUTDIR_RESULT
    mkdir -p $DATA_RESULT

    get_all_db_name $DATA_RESULT

    while read -r DB 
    do
      export_db_table_data $DB
    done  < `ls ${DATA_RESULT}/db.txt`
}

# 得到db列表
function get_all_db_name(){
    local OUT=$1
    clickhouse-client -h $CK_HOST -u $CK_USER --password="${CK_PWD}" --port=${CK_PORT} -q "SHOW DATABASES" > $OUT/db.txt

    for skip_db in "${SKIP_DATABASES[@]}";
    do
       sed -i '/^'${skip_db}'$/d' $OUT/db.txt
    done
}

# 得到db下的所有表
function get_db_all_tables(){
    local DB_NAME=$1
    local OUT=$2

    clickhouse-client -h $CK_HOST -u $CK_USER --password="${CK_PWD}" --port=${CK_PORT} -q "SHOW TABLES FROM $DB_NAME" > $OUT/table.txt

    for skip_table in "${SKIP_TABLES[@]}";
    do
        local SK_DB_NAME=$(echo "${skip_table}"|awk -F '.' '{print $1}')
        local SK_TB_NAME=$(echo "${skip_table}"|awk -F '.' '{print $2}')
        if [ $SK_DB_NAME = $DB_NAME ];then
            sed -i '/^'${skip_table}'$/d' $OUT/table.txt
        fi
    done
}

# 导入单表结构
function import_table_schema(){
    local DB_NAME=$1
    local SCHEMA_TSV=$2
    echo "import table schema $SCHEMA_TSV"
    cat $SCHEMA_TSV | clickhouse-client -h $CK_HOST -u $CK_USER --password="${CK_PWD}"  --port=${CK_PORT} -d ${DB_NAME}

}

# 导入库结构
function import_db_schema(){
    local DB_NAME=$1
    local SCHEMA_RESULT_DIR=$2
    for i in $(ls ${SCHEMA_RESULT_DIR}/$DB_NAME/schema/*.tsv);
    do 
        import_table_schema $DB_NAME $i
    done
}

# 导入目录下所有库表结构
function import_db_schema_all(){
    local SCHEMA_RESULT_DIR=$1
    for DB_NAME in $(ls ${SCHEMA_RESULT_DIR}|grep -v .txt);
    do
        import_db_schema $DB_NAME $SCHEMA_RESULT_DIR 
    done
}


function import_table_data(){
    local DB_NAME=$1
    local DATA_CSV=$2
    echo "import table data $DATA_CSV"
    local DB_TABLE_NAME=$(ls $DATA_CSV|awk -F '/' '{print $NF}'|awk -F '.' '{print $1 "." $2}')
    clickhouse-client -h $CK_HOST -u $CK_USER --password="${CK_PWD}"  --port=${CK_PORT} -d ${DB_NAME} -q "insert into ${DB_TABLE_NAME} FORMAT CSVWithNames" --format_csv_delimiter=','  < $DATA_CSV 
}

function import_db_data(){
    local DB_NAME=$1
    local RESULT_DIR=$2
    for i in $(ls ${RESULT_DIR}/$DB_NAME/data/*.csv);
    do 
        import_table_data $DB_NAME $i
    done
}

function import_db_data_all(){
    local RESULT_DIR=$1
    for DB_NAME in $(ls ${RESULT_DIR}|grep -v .txt);
    do
        import_db_data $DB_NAME $RESULT_DIR
    done;
    
}

function usage(){
  echo "
      $0 [paramter]
	 export_table_schema  [db.table]        导出指定表的表结构	
         export_db_schema     [db]              导出指定库的表结构                  
         export_db_schema_all                   导出除 system，information_schema，INFORMATION_SCHEMA 外的库表结构
         export_table_data    [db.table]        导出指定表数据   
         export_db_data       [db]              导出指定库的所有表数据
         export_db_data_all                     导出除 system，information_schema，INFORMATION_SCHEMA 外的库表数据
         import_table_schema  [db] [tsv_dir]    导入单表结构,传入参数为 db xxx/db.tb.tsv 
         import_db_schema     [db] [db_dir]     导入库的表结构，需要路径结构为 xxx/db/db.tb.tsv,传入 xxx
         import_db_schema_all 
         import_table_data    [db] [csv_dir]    导入单表数据
         import_db_data       [db] [db_dir]     导入单库的表数据，，需要路径结构为 xxx/db/db.tb.csv,传入 xxx
         import_db_data_all
         demo					操作示例
  "
}

function demo(){
  echo "
    导出单表结构：
      ./ck_export_import.sh export_table_schema db_1.testa
    导出指定库的表结构：
      ./ck_export_import.sh export_db_schema db_1
    导出除 system，information_schema，INFORMATION_SCHEMA 外的所有库表结构：
      ./ck_export_import.sh export_db_schema_all
    导入单表结构: 
      ./ck_export_import.sh import_table_schema db_1 /root/aa/export_data/db_1/schema/db_1.testa.tsv
    导入单个库表结构: 
      ./ck_export_import.sh import_db_schema db_1 /root/aa/export_data
    导入所有库表结构:
      ./ck_export_import.sh import_db_schema_all /root/aa/export_data
    导入单表：
      ./ck_export_import.sh import_table_schema db_1 /root/aa/export_data/db_1/schema/db_1.testa.tsv 
    导入单个库表结构：
      ./ck_export_import.sh import_db_schema db_1 /root/aa/export_data
    导入所有库表结构：
      ./ck_export_import.sh import_db_schema_all /root/aa/export_data
    单表数据导出：
      ./ck_export_import.sh export_table_data db_1.testa
    单库表数据导出：
      ./ck_export_import.sh export_db_data db_1
    全库表数据导出（除 system，information_schema，INFORMATION_SCHEMA）：
      ./ck_export_import.sh export_db_data_all
    导入单表数据：
      ./ck_export_import.sh import_table_data db_1 /root/aa/export_data/db_1/data/db_1.testa.csv 
    导入单库表数据：
      ./ck_export_import.sh import_db_data db_1 /root/aa/export_data
    导入全库表数据：
      ./ck_export_import.sh import_db_data_all /root/aa/export_data
"
}

# 入口
command="${1}"
command2="${2}"
command3="${3}"
case "${command}" in
    "export_table_schema" )
        export_table_schema $command2
    ;;
    "export_db_schema" )
        export_db_table_schema $command2
    ;;
    "export_db_schema_all" )
        export_db_table_schema_all
    ;;
    "export_table_data" )
        export_table_data $command2
    ;;
    "export_db_data" )
        export_db_table_data $command2
    ;;
    "export_db_data_all" )
        export_db_table_data_all
    ;;
    "import_table_schema" )
        import_table_schema $command2 $command3
    ;;
    "import_db_schema" )
        import_db_schema $command2 $command3
    ;;
    "import_db_schema_all" )
        import_db_schema_all $command2
    ;;
     "import_table_data" )
        import_table_data $command2 $command3
    ;;
    "import_db_data" )
        import_db_data $command2 $command3
    ;;
    "import_db_data_all" )
        import_db_data_all $command2
    ;;
    "demo" )
        demo
    ;;
    * )
        usage
    ;;
esac


